<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
                      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
    <title>Zend_Db Performance - Zend Framework Manual</title>

</head>
<body>
<table width="100%">
    <tr valign="top">
        <td width="85%">
            <table width="100%">
                <tr>
                    <td width="25%" style="text-align: left;">
                    <a href="performance.classloading.html">Class Loading</a>
                    </td>

                    <td width="50%" style="text-align: center;">
                        <div class="up"><span class="up"><a href="performance.html">Zend Gdata Performance Guide</a></span><br />
                        <span class="home"><a href="manual.html">Programmer's Reference Guide</a></span></div>
                    </td>

                    <td width="25%" style="text-align: right;">
                        <div class="next" style="text-align: right; float: right;"><a href="performance.localization.html">Internationalization (i18n) and Localization (l10n)</a></div>
                    </td>
                </tr>
            </table>
<hr />
<div id="performance.database" class="section"><div class="info"><h1 class="title">Zend_Db Performance</h1></div>
    

    <p class="para">
        <span class="classname">Zend_Db</span> is a database abstraction layer, and is intended to
        provide a common <acronym class="acronym">API</acronym> for <acronym class="acronym">SQL</acronym> operations.
        <span class="classname">Zend_Db_Table</span> is a
        Table Data Gateway, intended to abstract common table-level database
        operations. Due to their abstract nature and the &quot;magic&quot; they do under
        the hood to perform their operations, they can sometimes introduce
        performance overhead.
    </p>

    <div class="section" id="performance.database.tableMetadata" name="performance.database.tableMetadata"><div class="info"><h1 class="title">How can I reduce overhead introduced by Zend_Db_Table for
            retrieving table metadata?</h1></div>
        

        <p class="para">
            In order to keep usage as simple as possible, and also to support
            constantly changing schemas during development,
            <span class="classname">Zend_Db_Table</span> does some magic under the hood: on
            first use, it fetches the table schema and stores it within object
            members. This operation is typically expensive, regardless of the
            database -- which can contribute to bottlenecks in production.
        </p>

        <p class="para">
            Fortunately, there are techniques for improving the situation.
        </p>

        <div class="section" id="performance.database.tableMetadata.cache" name="performance.database.tableMetadata.cache"><div class="info"><h1 class="title">Use the metadata cache</h1></div>
            

            <p class="para">
                <span class="classname">Zend_Db_Table</span> can optionally utilize
                <span class="classname">Zend_Cache</span> to cache table metadata. This is
                typically faster to access and less expensive than fetching the
                metadata from the database itself.
            </p>

            <p class="para">
                The <a href="" class="link"><span class="classname">Zend_Db_Table
                </span> documentation includes information on metadata caching</a>.
            </p>
        </div>

        <div class="section" id="performance.database.tableMetadata.hardcoding" name="performance.database.tableMetadata.hardcoding"><div class="info"><h1 class="title">Hardcode your metadata in the table definition</h1></div>
            

            <p class="para">
                As of 1.7.0, <span class="classname">Zend_Db_Table</span> also provides <a href="" class="link">support
                for hardcoding metadata in the table definition</a>. This is
                an advanced use case, and should only be used when you know the
                table schema is unlikely to change, or that you&#039;re able to keep
                the definitions up-to-date.
            </p>
        </div>
    </div>

    <div class="section" id="performance.database.select" name="performance.database.select"><div class="info"><h1 class="title">
            SQL generated with Zend_Db_Select s not hitting my indexes; how can I make it better?
        </h1></div>
        

        <p class="para">
            <span class="classname">Zend_Db_Select</span> is relatively good at its job. However,
            if you are performing complex queries requiring joins or
            sub-selects, it can often be fairly naive.
        </p>

        <div class="section" id="performance.database.select.writeyourown" name="performance.database.select.writeyourown"><div class="info"><h1 class="title">Write your own tuned SQL</h1></div>
            

            <p class="para">
                The only real answer is to write your own <acronym class="acronym">SQL</acronym>;
                <span class="classname">Zend_Db</span> does not require the usage of
                <span class="classname">Zend_Db_Select</span>, so providing your own, tuned
                <acronym class="acronym">SQL</acronym> select statements is a perfectly legitimate approach,
            </p>

            <p class="para">
                Run <b><tt>EXPLAIN</tt></b> on your queries, and test a variety of
                approaches until you can reliably hit your indices in the most
                performant way -- and then hardcode the <acronym class="acronym">SQL</acronym> as a class property
                or constant.
            </p>

            <p class="para">
                If the <acronym class="acronym">SQL</acronym> requires variable arguments, provide placeholders in
                the <acronym class="acronym">SQL</acronym>, and utilize a combination of
                 <span class="methodname">vsprintf()</span> and  <span class="methodname">array_map()</span> to
                inject the values into the <acronym class="acronym">SQL</acronym>:
            </p>

            <div class="programlisting php"><div class="phpcode"><div class="php" style="font-family: monospace;"><ol><li style="font-family: 'Courier New', Courier, monospace; color: black; font-weight: normal; font-style: normal;"><div style="font-family: 'Courier New', Courier, monospace; font-weight: normal;"><span style="color: #808080; font-style: italic;">// $adapter is the DB adapter. In Zend_Db_Table, retrieve</span></div></li>
<li style="font-family: 'Courier New', Courier, monospace; color: black; font-weight: normal; font-style: normal;"><div style="font-family: 'Courier New', Courier, monospace; font-weight: normal;"><span style="color: #808080; font-style: italic;">// it using $this-&gt;getAdapter().</span></div></li>
<li style="font-family: 'Courier New', Courier, monospace; color: black; font-weight: normal; font-style: normal;"><div style="font-family: 'Courier New', Courier, monospace; font-weight: normal;"><span style="color: #0000ff;">$sql</span> = <a href="http://www.php.net/vsprintf"><span style="color: #000066;">vsprintf</span></a><span style="color: #66cc66;">&#40;</span></div></li>
<li style="font-family: 'Courier New', Courier, monospace; color: black; font-weight: normal; font-style: normal;"><div style="font-family: 'Courier New', Courier, monospace; font-weight: normal;">&nbsp; &nbsp; self::<span style="color: #006600;">SELECT_FOO</span>,</div></li>
<li style="font-family: 'Courier New', Courier, monospace; color: black; font-weight: normal; font-style: normal;"><div style="font-family: 'Courier New', Courier, monospace; font-weight: normal;">&nbsp; &nbsp; <a href="http://www.php.net/array_map"><span style="color: #000066;">array_map</span></a><span style="color: #66cc66;">&#40;</span><a href="http://www.php.net/array"><span style="color: #000066;">array</span></a><span style="color: #66cc66;">&#40;</span><span style="color: #0000ff;">$adapter</span>, <span style="color: #ff0000;">'quoteInto'</span><span style="color: #66cc66;">&#41;</span>, <span style="color: #0000ff;">$values</span><span style="color: #66cc66;">&#41;</span></div></li>
<li style="font-family: 'Courier New', Courier, monospace; color: black; font-weight: normal; font-style: normal;"><div style="font-family: 'Courier New', Courier, monospace; font-weight: normal;"><span style="color: #66cc66;">&#41;</span>;</div></li></ol></div></div></div>

        </div>
    </div>
</div>
        <hr />

            <table width="100%">
                <tr>
                    <td width="25%" style="text-align: left;">
                    <a href="performance.classloading.html">Class Loading</a>
                    </td>

                    <td width="50%" style="text-align: center;">
                        <div class="up"><span class="up"><a href="performance.html">Zend Gdata Performance Guide</a></span><br />
                        <span class="home"><a href="manual.html">Programmer's Reference Guide</a></span></div>
                    </td>

                    <td width="25%" style="text-align: right;">
                        <div class="next" style="text-align: right; float: right;"><a href="performance.localization.html">Internationalization (i18n) and Localization (l10n)</a></div>
                    </td>
                </tr>
            </table>
</td>
        <td style="font-size: smaller;" width="15%"> <style type="text/css">
#leftbar {
	float: left;
	width: 186px;
	padding: 5px;
	font-size: smaller;
}
ul.toc {
	margin: 0px 5px 5px 5px;
	padding: 0px;
}
ul.toc li {
	font-size: 85%;
	margin: 1px 0 1px 1px;
	padding: 1px 0 1px 11px;
	list-style-type: none;
	background-repeat: no-repeat;
	background-position: center left;
}
ul.toc li.header {
	font-size: 115%;
	padding: 5px 0px 5px 11px;
	border-bottom: 1px solid #cccccc;
	margin-bottom: 5px;
}
ul.toc li.active {
	font-weight: bold;
}
ul.toc li a {
	text-decoration: none;
}
ul.toc li a:hover {
	text-decoration: underline;
}
</style>
 <ul class="toc">
  <li class="header home"><a href="manual.html">Programmer's Reference Guide</a></li>
  <li class="header up"><a href="manual.html">Programmer's Reference Guide</a></li>
  <li class="header up"><a href="performance.html">Zend Gdata Performance Guide</a></li>
  <li><a href="performance.introduction.html">Introduction</a></li>
  <li><a href="performance.classloading.html">Class Loading</a></li>
  <li class="active"><a href="performance.database.html">Zend_Db Performance</a></li>
  <li><a href="performance.localization.html">Internationalization (i18n) and Localization (l10n)</a></li>
  <li><a href="performance.view.html">View Rendering</a></li>
 </ul>
 </td>
    </tr>
</table>
</body>
</html>